using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class Link : ILink
    {
        // Methods
        public bool DeleteLink(int id)
        {
            string cmdText = "UPDATE LINK_SITE_INFO SET ISDEL='1' WHERE Link_id = " + id;
            SqlParameter[] cmdParms = new SqlParameter[0];
            try
            {
                using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool DeleteMenuLink(int menu_id)
        {
            string cmdText = "update  MENU_LINK_INFO set ISDEl='1'where Link_id=" + menu_id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, null);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        public IList GetLink()
        {
            string cmdText = "select Link_id,ISNULL(Link_title,''),ISNULL(Link_url,''),ISNULL(Link_type,''),ISNULL(Link_desc,''),ISNULL(Link_pic,''),Create_date,ISDEL from LINK_SITE_INFO where ISDEL='0' order by Link_id desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    LinkInfo info = new LinkInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3));
                    info.Link_desc = reader.GetString(4);
                    info.Link_pic = reader.GetString(5);
                    list.Add(info);
                }
            }
            return list;
        }

        public LinkInfo GetLinkInfo(int linkID)
        {
            string cmdText = "select Link_id,ISNULL(Link_title,''),ISNULL(Link_url,''),ISNULL(Link_type,''),ISNULL(Link_desc,''),ISNULL(Link_pic,''),Create_date,ISDEL from LINK_SITE_INFO where ISDEL='0' and Link_id =" + linkID + " order by Link_id desc";
            IList list = new ArrayList();
            LinkInfo info = null;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                if (reader.Read())
                {
                    info = new LinkInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3));
                    info.Link_desc = reader.GetString(4);
                    info.Link_pic = reader.GetString(5);
                }
            }
            return info;
        }

        public IList getMenuLink(int menu_id)
        {
            return this.getMenuLink(menu_id, -1);
        }

        public IList getMenuLink(int menu_id, int flag)
        {
            string cmdText = "select Link_id,Link_title,Link_url,Link_desc,Link_pic,Link_type,Menu_id, target from MENU_LINK_INFO where IsDel='0' and Menu_id=" + menu_id;
            if (flag >= 0)
            {
                cmdText = cmdText + " and Link_type = " + flag;
            }
            cmdText = cmdText + " order by sorted desc";
            IList list = new ArrayList();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    LinkInfo info = new LinkInfo();
                    info.Link_id = reader.GetInt32(0);
                    info.Link_title = reader.GetString(1);
                    info.Link_url = reader.GetString(2);
                    info.Link_desc = reader.GetString(3);
                    info.Link_pic = reader.GetString(4);
                    info.Link_type = reader.GetInt32(5);
                    info.Menu_id = reader.GetInt32(6);
                    info.target = reader.GetString(7);
                    list.Add(info);
                }
            }
            return list;
        }

        public LinkInfo getMenuLink_Data(int menu_id)
        {
            LinkInfo info = new LinkInfo();
            string str = "select Link_id,Link_title,Link_url,Link_desc,Link_pic,Link_type,Menu_id,Sorted,target from MENU_LINK_INFO where IsDel='0' and Link_id=";
            IList list = new ArrayList();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, str + menu_id, null))
            {
                while (reader.Read())
                {
                    info.Link_id = reader.GetInt32(0);
                    info.Link_title = reader.GetString(1);
                    info.Link_url = reader.GetString(2);
                    info.Link_desc = reader.GetString(3);
                    info.Link_pic = reader.GetString(4);
                    info.Link_type = reader.GetInt32(5);
                    info.Menu_id = reader.GetInt32(6);
                    info.Sorted = reader.GetInt32(7);
                    info.target = reader.GetString(8);
                }
            }
            return info;
        }

        public bool InsertLink(LinkInfo info)
        {
            string cmdText = string.Concat(new object[] { "insert into LINK_SITE_INFO ( Link_title,Link_url,Link_desc,Link_pic,Link_type,Create_date,ISDEL ) values('", StringUtil.ReplaceStr(info.Link_title), "','", StringUtil.ReplaceStr(info.Link_url), "','", StringUtil.ReplaceStr(info.Link_desc), "','", StringUtil.ReplaceStr(info.Link_pic), "',", info.Link_type, ",getdate(),0 )" });
            SqlParameter[] cmdParms = new SqlParameter[0];
            try
            {
                using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool InsertMenuLink(LinkInfo link)
        {
            string cmdText = string.Concat(new object[] { 
            "insert into  MENU_LINK_INFO (Link_title,Link_url,Link_desc,Link_pic,Link_type,Menu_id,Sorted,target)values('", link.Link_title, "','", link.Link_url, "','", link.Link_desc, "','", link.Link_pic, "',", link.Link_type, ",", link.Menu_id, ",", link.Sorted, ",'", link.target, 
            "') "
         });
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, null);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        public bool UpdateLink(LinkInfo info)
        {
            string cmdText = string.Concat(new object[] { " Update LINK_SITE_INFO set Link_title ='", StringUtil.ReplaceStr(info.Link_title), "',Link_url ='", StringUtil.ReplaceStr(info.Link_url), "',Link_desc ='", StringUtil.ReplaceStr(info.Link_desc), "',Link_pic = '", StringUtil.ReplaceStr(info.Link_pic), "',Link_type =", info.Link_type, " where Link_id =", info.Link_id });
            SqlParameter[] cmdParms = new SqlParameter[0];
            try
            {
                using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool UpdateMenuLink(LinkInfo link)
        {
            string cmdText = string.Concat(new object[] { "update menu_link_info set link_title='", link.Link_title, "',link_url='", link.Link_url, "',link_desc='", link.Link_desc, "',link_pic='", link.Link_pic, "',link_type=", link.Link_type, ",target='", link.target, "',Sorted=", link.Sorted, " where link_id=", link.Link_id });
            try
            {
                using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, null);
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
